﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.SqlClient;
using System.Text;
using System.Threading.Tasks;
using KonyvKolcsonzes;

namespace Konyvkolcsonzo
{
    struct peldany {
        public string id;
        public long konyvID;
        public string cim;
        public long kiadoID;
        public string kiado;
        public int ev;
        public int oldal;
        public int ar;
    }

    class Peldanyok : DAL
    {
        public List<peldany> getPeldany(string konyvcim)
        {
            openConnection();
            string error = "";
            List<peldany> nevsor = new List<peldany>();
            SqlDataReader reader = executeReader("select PeldanyID, Peldanyok.KonyvID AS IDkonyv, Cim, Peldanyok.KiadoID AS IDk, KiadoNev, MegjelenesiEv, Oldalszam, Ar from Peldanyok where Cim like '" + konyvcim + "'", ref error);
            while (reader.Read())
            {
                peldany p;
                p.id = Convert.ToString(reader["PeldanyID"]);
                p.konyvID = Convert.ToInt32(reader["IDkonyv"]);
                p.cim = Convert.ToString(reader["Cim"]);
                p.kiadoID = Convert.ToInt32(reader["IDk"]);
                p.kiado = Convert.ToString(reader["KiadoNev"]);
                p.ev = Convert.ToInt32(reader["MegjelenesiEv"]);
                p.oldal = Convert.ToInt32(reader["Oldalszam"]);
                p.ar = Convert.ToInt32(reader["Ar"]);
                p.id = p.cim.Replace(" ", "").Replace(",", "").Substring(0, 4).ToUpper() + p.ev.ToString().Substring(2) + p.id;
                nevsor.Add(p);
            }
            closeConnection();
            return nevsor;
        }

        public List<string> getKonyvek()
        {
            openConnection();
            string error = "";
            List<string> nevsor = new List<string>();
            SqlDataReader reader = executeReader("select Cim from Konyvek", ref error);
            while (reader.Read())
            {
                nevsor.Add(Convert.ToString(reader["Cim"]));
            }
            closeConnection();
            return nevsor;
        }

        public List<string> getKiadok()
        {
            openConnection();
            string error = "";
            List<string> nevsor = new List<string>();
            SqlDataReader reader = executeReader("select KiadoNev from Kiadok", ref error);
            while (reader.Read())
            {
                nevsor.Add(Convert.ToString(reader["KiadoNev"]));
            }
            closeConnection();
            return nevsor;
        }

        public long getKiadoID(string kiadoNev)
        {
            openConnection();
            string error = "";
            long nevsor = new long();
            SqlDataReader reader = executeReader("select KiadoId from Kiadok where KiadoNev like '" + kiadoNev + "'", ref error);
            while (reader.Read())
            {
                nevsor = Convert.ToInt64(reader["KiadoId"]);
            }
            closeConnection();
            return nevsor;
        }

        public long getKonyvID(string konyvCim)
        {
            openConnection();
            string error = "";
            long nevsor = new long();
            SqlDataReader reader = executeReader("select KonyvID from Konyvek where Cim like '" + konyvCim + "'", ref error);
            while (reader.Read())
            {
                nevsor = Convert.ToInt64(reader["KonyvID"]);
            }
            closeConnection();
            return nevsor;
        }

        public string insertPeldany(peldany k)
        {
            k.konyvID = getKonyvID(k.cim);
            k.kiadoID = getKiadoID(k.kiado);
            openConnection();
            string error = executeInsertCommand("INSERT into Peldany (KonyvID, KiadoID, MegjelenesiEv, Oldalszam, Ar) values  ('" + k.konyvID + "', '" + k.kiadoID + "', '" + k.ev + "', '" + k.oldal + "','" + k.ar + "')");
            closeConnection();
            return error;
        }

        public string updatePeldany(peldany k)
        {
            k.konyvID = getKonyvID(k.cim);
            k.kiadoID = getKiadoID(k.kiado);
            openConnection();
            string error = executeUpdateCommand("update Peldany set KonyvID = " + k.konyvID + ", kiadoID = " + k.kiadoID + ", MegjelenesiEv = " + k.ev + ", Oldalszam = " + k.oldal + ", Ar = '" + k.ar + "' where PeldanyID = " + k.id + " ");
            closeConnection();
            return error;
        }

        public string deletePeldany(long id)
        {
            deleteKikolcs(id);
            openConnection();
            string error = executeDeleteCommand("delete from Peldany where PeldanyID = " + id);
            closeConnection();
            return error;
        }

        public string deleteKikolcs(long id)
        {
            openConnection();
            string error = executeDeleteCommand("delete from Kikolcsonozve where PeldanyID = " + id);
            closeConnection();
            return error;
        }
    }
}
